package com.miracle.erp.dao.Impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.miracle.erp.util.DBUtil;
import com.miracle.erp.model.User;
import com.miracle.erp.dao.IUserDao;

public class UserDao implements IUserDao {

	public User loadByUsername(String username) {
		// TODO 数据库查询 要使用jdbc 工具3个样导入
		// 创建连接
		// 局部变量 不赋值不能使用
		Connection connection = null;
		// 创建语句传输对象 导入java.sql包
		PreparedStatement perparedStatement = null;
		ResultSet resultSet = null;
		try {
			// 调用 工具类 与数据库建立连接 登陆数据库
			connection = DBUtil.getConnection();
			// 准备查询 的sql语句
			String sql = "select * from t_user where username = ?";
			// 用连接对象传入sql
			perparedStatement = connection.prepareStatement(sql);
			// 设置传入的位置 第一个是username
			perparedStatement.setString(1, username);
			// executeQuery()方法会把数据库响应的查询结果存放在ResultSet类对象中供我们使用
			resultSet = perparedStatement.executeQuery();

			// 现在获得了信息 创建一个对象 将信息存储起来
			User user = null;
			if (resultSet.next()) {
				user = new User(resultSet.getInt("id"),
						resultSet.getString("username"),
						resultSet.getString("password"),
						resultSet.getString("nickname"),
						resultSet.getInt("age"), resultSet.getInt("sex"));
			}
			return user;

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet);
			DBUtil.close(perparedStatement);
			DBUtil.close(connection);
		}
		return null;
	}

	/*
	 * 查询所有用户
	 * 
	 * @see dao.IUserDao#list()
	 */
	@Override
	public List<User> list() {
		Connection connection = null;
		PreparedStatement perparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			// 查询所有信息 按id 升序
			String sql = "select * from t_user order by id asc";
			perparedStatement = connection.prepareStatement(sql);
			resultSet = perparedStatement.executeQuery();
			// 得到的数据是一个个对象的
			// 创建一个列表List用来存储数据
			List<User> users = new ArrayList<User>();
			// 死循环获取数据
			while (resultSet.next()) {
				// 创建对象获取并添加到数组存储数据
				User user = new User(resultSet.getInt("id"),
						resultSet.getString("username"),
						resultSet.getString("password"),
						resultSet.getString("nickname"),
						resultSet.getInt("age"), resultSet.getInt("sex"));
				// 添加
				users.add(user);
			}
			// 返回数组
			return users;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet);
			DBUtil.close(perparedStatement);
			DBUtil.close(connection);
		}
		// 必须有返回值 上述存在不执行情况
		return null;
	}

	/*
	 * (删除管理员) 要做的是查找 然后删掉 不需要返回任何信息
	 * 
	 * @see dao.IUserDao#delete(model.User)
	 */
	@Override
	public void delete(User user) {
		String username = user.getUsername();
		Connection connection = null;
		PreparedStatement perparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			// 错误： 在所有信息中查询username
			// 这里做的是直接删除的操作 delete from 表名 where 列名 = 值 ；
			String sql = "delete  from t_user where username = ?";
			perparedStatement = connection.prepareStatement(sql);
			// 上面的问号怎么获取值
			// 设置它set方法
			perparedStatement.setString(1, username);
			perparedStatement.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(perparedStatement);
			DBUtil.close(connection);
		}

	}

	@Override
	public void add(User user) {
		// TODO 数据库添加数据
		// 连接数据库，创建传输语句，执行sql，关闭资源
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = DBUtil.getConnection();
			// sql 语句
			String sql = "insert into t_user (username,password,nickname,age,sex) values (?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getUsername());
			preparedStatement.setString(3, user.getNickname());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setInt(5, user.getSex());
			preparedStatement.setInt(4, user.getAge());
			preparedStatement.executeUpdate();

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.close(preparedStatement);
			DBUtil.close(connection);
		}
	}

	@Override
	public User load(int id) {
		Connection connection = null;
		PreparedStatement perparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			String sql = "select * from t_user where id = ?";
			perparedStatement = connection.prepareStatement(sql);
			perparedStatement.setInt(1, id);
			resultSet = perparedStatement.executeQuery();
			User user = null;
			if (resultSet.next()) {
				user = new User(resultSet.getInt("id"),
						resultSet.getString("username"),
						resultSet.getString("password"),
						resultSet.getString("nickname"),
						resultSet.getInt("age"), resultSet.getInt("sex"));
			}
			return user;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(resultSet);
			DBUtil.close(perparedStatement);
			DBUtil.close(connection);
		}
		return null;
	}
/**
 * 其实 无需返回值
 */
	@Override
	public void update(User user) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		
		// 不需要获取结果 传递  因为在这里直接更新完比数据库   更新之后跳转的展示页面 展示页面的代码执行 去数据库中查询信息输出
//		ResultSet resultSet = null;
		try {
			connection = DBUtil.getConnection();
			// sql 语句  UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
			String sql = "update t_user set username = ?,password = ? ,nickname= ?,age = ? ,sex = ? where id = ? ";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, user.getUsername());
			preparedStatement.setString(3, user.getNickname());
			preparedStatement.setString(2, user.getPassword());
			preparedStatement.setInt(5, user.getSex());
			preparedStatement.setInt(4, user.getAge());
			preparedStatement.setInt(6, user.getId());
			preparedStatement.executeUpdate();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
//			DBUtil.close(resultSet);
			DBUtil.close(preparedStatement);
			DBUtil.close(connection);
		}
//		return user;
	}
}